﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GIMS.MODEL;

namespace KPIS.GERP.GIMS.DAL
{
    public sealed class DeliveryNoteDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public DeliveryNoteDAL()
        {
        
        }

        public DeliveryNoteInfo GetDeliveryNote(Nullable<int> dn_seq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            DeliveryNoteInfo info = new DeliveryNoteInfo();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@dn_seq", dn_seq);

                strSQL = "SELECT dn.*"
                        + " , rs.RECORD_STATUS_NAME"
                    + " FROM ICM_DELIVERY_NOTE dn"
                        + " INNER JOIN SYS_RECORD_STATUS rs ON dn.RECORD_STATUS = rs.RECORD_STATUS"
                    + " WHERE dn.ICM_DN_SEQ = @dn_seq";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    BidderInfo infoBidder = new BidderInfo();
                    PurchaseOrderInfo infoPo = new PurchaseOrderInfo();
                    MasterWarehouseInfo infoWarehouse = new MasterWarehouseInfo();
                    PurchaseOrderInstallmentInfo infoPoInstallment = new PurchaseOrderInstallmentInfo();
                    MasterContactInfo infoContact = new MasterContactInfo();
                    SysRecordStatusInfo infoRecordStatus = new SysRecordStatusInfo();

                    info.Seq = Convert.ToInt32(dr[0]["ICM_DN_SEQ"]);
                    infoPo.Seq = Convert.ToInt32(dr[0]["ICM_PO_SEQ"]);
                    infoPoInstallment.Seq = Convert.ToInt32(dr[0]["ICM_PO_INSTALL_SEQ"]);
                    info.DeliveryDate = (Convert.IsDBNull(dr[0]["DELIVERY_DATE"]) ? null : (Nullable<DateTime>)(dr[0]["DELIVERY_DATE"]));
                    info.RefNo = dr[0]["ICM_DN_REF_NO"].ToString();
                    info.Name = dr[0]["ICM_DN_NAME"].ToString();
                    info.Descr = dr[0]["ICM_DN_DESCR"].ToString();
                    info.ReceiveDate = (Convert.IsDBNull(dr[0]["RECEIVE_DATE"]) ? null : (Nullable<DateTime>)(dr[0]["RECEIVE_DATE"]));
                    info.InvoiceDate = (Convert.IsDBNull(dr[0]["INVOICE_DATE"]) ? null : (Nullable<DateTime>)(dr[0]["INVOICE_DATE"]));
                    info.InvoiceNumber = dr[0]["INVOICE_NUMBER"].ToString();
                    infoWarehouse.Seq = (Convert.IsDBNull(dr[0]["ICM_WH_SEQ"]) ? null : (Nullable<int>)(dr[0]["ICM_WH_SEQ"]));
                    info.DocumentNumber = dr[0]["DOCUMENT_NUMBER"].ToString();
                    info.DeliveryPlace = dr[0]["DELIVERY_PLACE"].ToString();
                    info.RedemptionAmount = (Convert.IsDBNull(dr[0]["REDEMPTION_AMOUNT"]) ? null : (Nullable<double>)(dr[0]["REDEMPTION_AMOUNT"]));
                    info.DeductionAmount = (Convert.IsDBNull(dr[0]["DEDUCTION_AMOUNT"]) ? null : (Nullable<double>)(dr[0]["DEDUCTION_AMOUNT"]));
                    info.BeExpenses = (Convert.IsDBNull(dr[0]["EXPENSES"]) ? null : (Nullable<int>)(dr[0]["EXPENSES"]));
                    
                    infoRecordStatus.Status = dr[0]["RECORD_STATUS"].ToString();
                    infoRecordStatus.Name = dr[0]["RECORD_STATUS_NAME"].ToString();

                    infoBidder.InfoMasContact = infoContact;
                    infoPo.InfoBidder = infoBidder;
                    info.InfoPo = infoPo;
                    info.InfoPoInstallment = infoPoInstallment;
                    info.InfoWarehouse = infoWarehouse;
                    info.InfoRecordStatus = infoRecordStatus;
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

            
        public IList<DeliveryNoteInfo> GetDeliveryNote(Nullable<int> YearTypeSeq, Nullable<int> Year, Nullable<int> PurchaseMethodSeq
            , Nullable<int> PurchaseTypeSeq, Nullable<int> OperationNameSeq, string Status)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<DeliveryNoteInfo> infoList = new List<DeliveryNoteInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@no_status", SystemInfo._NoStatus);
                dbManager.AddParameters(2, "@Status", Status);

                strSQL = "SELECT main.*"
                        + " FROM ("
                        + " SELECT dn.ICM_DN_SEQ"
                            + " , CASE WHEN po.CAN_INSTALLMENT = @no_status THEN NULL ELSE (select distinct icm_po_install_number from icm_purchase_order_installment where icm_po_install_seq = dn.icm_po_install_seq) END AS INSTALL_NUMBER"
                            + " , pr.ICM_PC_TYPE_SEQ"
                            + " , pt.ICM_PC_TYPE_NAME"
                            + " , dn.DELIVERY_DATE"
                            + " , dn.ICM_DN_REF_NO"
                            + " , dn.ICM_DN_NAME"
                            + " , CONCAT(c.ICM_CON_NAME_TH, COALESCE(CONCAT('(', c.ICM_CON_NAME_ENG, ')'),'')) AS CONTACT_NAME"
                            + " , rs.RECORD_STATUS_NAME"
                            + " , dn.YEAR_TYPE_SEQ"
                            + " , dn.BGM_YEAR"
                            + " , pr.ICM_PC_METHOD_SEQ"
                            + " , pm.ICM_PC_METHOD_NAME"
                            + " , dn.RECORD_STATUS"
                            + ", pot.ICM_PO_TYPE_NAME"
                        + " FROM ICM_DELIVERY_NOTE dn"
                            + " INNER JOIN ICM_PURCHASE_ORDER po ON dn.ICM_PO_SEQ = po.ICM_PO_SEQ"
                            + " INNER JOIN ICM_SYS_PO_TYPE pot ON po.ICM_PO_TYPE_SEQ = pot.ICM_PO_TYPE_SEQ"
                            + " INNER JOIN ICM_BIDDER b ON po.ICM_BIDDER_SEQ = b.ICM_BIDDER_SEQ"
                            + " INNER JOIN ICM_BID bid ON bid.ICM_BID_SEQ = b.ICM_BID_SEQ"
                            + " INNER JOIN ICM_MAS_CONTACT c ON b.ICM_CON_SEQ = c.ICM_CON_SEQ"
                            + " INNER JOIN ICM_PURCHASE_REQUEST pr ON pr.ICM_PR_SEQ = bid.ICM_PR_SEQ"
                            + " INNER JOIN ICM_MAS_PURCHASE_METHOD pm ON pr.ICM_PC_METHOD_SEQ = pm.ICM_PC_METHOD_SEQ"
                            + " INNER JOIN ICM_MAS_PURCHASE_TYPE pt ON pr.ICM_PC_TYPE_SEQ = pt.ICM_PC_TYPE_SEQ"
                            + " INNER JOIN SYS_RECORD_STATUS rs ON dn.RECORD_STATUS = rs.RECORD_STATUS"
                        + " UNION ALL"
                        + " SELECT dn.ICM_DN_SEQ"
                            + " , CASE WHEN po.CAN_INSTALLMENT = @no_status THEN NULL ELSE (select distinct icm_po_install_number from icm_purchase_order_installment where icm_po_install_seq = dn.icm_po_install_seq) END AS INSTALL_NUMBER"
                            + " , pr.ICM_PC_TYPE_SEQ"
                            + " , pt.ICM_PC_TYPE_NAME"
                            + " , dn.DELIVERY_DATE"
                            + " , dn.ICM_DN_REF_NO"
                            + " , dn.ICM_DN_NAME"
                            + " , CONCAT(c.ICM_CON_NAME_TH, COALESCE(CONCAT('(', c.ICM_CON_NAME_ENG, ')'),'')) AS CONTACT_NAME"
                            + " , rs.RECORD_STATUS_NAME"
                            + " , dn.YEAR_TYPE_SEQ"
                            + " , dn.BGM_YEAR"
                            + " , pr.ICM_PC_METHOD_SEQ"
                            + " , pm.ICM_PC_METHOD_NAME"
                            + " , dn.RECORD_STATUS"
                            + ", pot.ICM_PO_TYPE_NAME"
                        + " FROM ICM_DELIVERY_NOTE dn"
                            + " INNER JOIN ICM_PURCHASE_ORDER po ON dn.ICM_PO_SEQ = po.ICM_PO_SEQ"
                            + " INNER JOIN ICM_SYS_PO_TYPE pot ON po.ICM_PO_TYPE_SEQ = pot.ICM_PO_TYPE_SEQ"
                            + " INNER JOIN ICM_BIDDER b ON po.ICM_BIDDER_SEQ = b.ICM_BIDDER_SEQ"
                            + " INNER JOIN ICM_MAS_CONTACT c ON b.ICM_CON_SEQ = c.ICM_CON_SEQ"
                            + " INNER JOIN ICM_PURCHASE_REQUEST pr ON pr.ICM_PR_SEQ = b.ICM_PR_SEQ"
                            + " INNER JOIN ICM_MAS_PURCHASE_METHOD pm ON pr.ICM_PC_METHOD_SEQ = pm.ICM_PC_METHOD_SEQ"
                            + " INNER JOIN ICM_MAS_PURCHASE_TYPE pt ON pr.ICM_PC_TYPE_SEQ = pt.ICM_PC_TYPE_SEQ"
                            + " INNER JOIN SYS_RECORD_STATUS rs ON dn.RECORD_STATUS = rs.RECORD_STATUS"
                        + ") main"
                        + " WHERE RECORD_STATUS <> @delete_status";
                strSQL += (YearTypeSeq == null) ? "" : " and YEAR_TYPE_SEQ = " + YearTypeSeq;
                strSQL += (Year == null) ? "" : " and BGM_YEAR = " + Year;
                strSQL += (PurchaseMethodSeq == null) ? "" : " and ICM_PC_METHOD_SEQ = " + PurchaseMethodSeq;
                strSQL += (PurchaseTypeSeq == null) ? "" : " and ICM_PC_TYPE_SEQ = " + PurchaseTypeSeq;
                strSQL += (string.IsNullOrEmpty(Status) ? "" : " and RECORD_STATUS = @Status");

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        DeliveryNoteInfo info = new DeliveryNoteInfo();
                        PurchaseOrderInfo infoPo = new PurchaseOrderInfo();
                        BidderInfo infoBidder = new BidderInfo();
                        PurchaseOrderInstallmentInfo infoPoInstallment = new PurchaseOrderInstallmentInfo();
                        PurchaseRequestInfo infoPr = new PurchaseRequestInfo();
                        MasterPurchaseTypeInfo infoPcType = new MasterPurchaseTypeInfo();
                        MasterContactInfo infoContact = new MasterContactInfo();
                        SysRecordStatusInfo infoRecordStatus = new SysRecordStatusInfo();

                        info.Seq = Convert.ToInt32(dr[i]["ICM_DN_SEQ"]);
                        infoPoInstallment.InstallNumber = (Convert.IsDBNull(dr[i]["INSTALL_NUMBER"]) ? null : (Nullable<int>)(dr[i]["INSTALL_NUMBER"]));
                        infoPcType.Seq = Convert.ToInt32(dr[i]["ICM_PC_TYPE_SEQ"]);
                        infoPcType.Name = dr[i]["ICM_PC_TYPE_NAME"].ToString();
                        info.DeliveryDate = (Convert.IsDBNull(dr[i]["DELIVERY_DATE"]) ? null : (Nullable<DateTime>)(dr[i]["DELIVERY_DATE"]));
                        info.RefNo = dr[i]["ICM_DN_REF_NO"].ToString();
                        info.Name = dr[i]["ICM_DN_NAME"].ToString();
                        infoContact.Name_th = dr[i]["CONTACT_NAME"].ToString();
                        infoRecordStatus.Name = dr[i]["RECORD_STATUS_NAME"].ToString();
                        info.DeliveryType = dr[i]["ICM_PO_TYPE_NAME"].ToString() + dr[i]["ICM_PC_TYPE_NAME"].ToString();

                        infoPr.InfoPurchaseType = infoPcType;
                        infoBidder.InfoPurchaseRequest = infoPr;
                        infoBidder.InfoMasContact = infoContact;
                        infoPo.InfoBidder = infoBidder;
                        info.InfoPo = infoPo;
                        info.InfoPoInstallment = infoPoInstallment;
                        info.InfoRecordStatus = infoRecordStatus;
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public string Insert(DeliveryNoteInfo info, IList<DeliveryNoteItemInfo> infoListItem, IList<DeliveryNoteItemExtraInfo> infoListExtra, IList<DeliveryNoteCommitteeInfo> infoListCommittee, IList<DeliveryNotePenaltyInfo> infoListPenalty, IList<DocumentInfo> infoListFile)
        {
            string strSQL;
            DataSet ds;
            int lastSeq = 0;
            string lastNumber = "";
            DeliveryNoteItemDAL dalDnItem = new DeliveryNoteItemDAL();
            DeliveryNoteItemExtraDAL dalDnExtra = new DeliveryNoteItemExtraDAL();
            DeliveryNoteCommitteeDAL dalDnCommittee = new DeliveryNoteCommitteeDAL();
            DeliveryNotePenaltyDAL dalDnPenalty = new DeliveryNotePenaltyDAL();
            DocumentDAL dalDocument = new DocumentDAL();
            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(23);

                dbManager.AddParameters(0, "@po_seq", info.InfoPo.Seq);
                dbManager.AddParameters(1, "@install_seq", info.InfoPoInstallment.Seq);
                dbManager.AddParameters(2, "@name", info.Name);
                dbManager.AddParameters(3, "@descr", info.Descr);
                dbManager.AddParameters(4, "@date", info.DeliveryDate);
                dbManager.AddParameters(5, "@receive_date", info.ReceiveDate);
                dbManager.AddParameters(6, "@invoice_no", info.InvoiceNumber);
                dbManager.AddParameters(7, "@invoice_date", info.InvoiceDate);
                dbManager.AddParameters(8, "@wh_seq", info.InfoWarehouse.Seq);
                dbManager.AddParameters(9, "@doc_no", info.DocumentNumber);
                dbManager.AddParameters(10, "@redemption_amount", info.RedemptionAmount);
                dbManager.AddParameters(11, "@deduction_amount", info.DeductionAmount);
                dbManager.AddParameters(12, "@place", info.DeliveryPlace);
                dbManager.AddParameters(13, "@year_type_seq", info.InfoYearType.Seq);
                dbManager.AddParameters(14, "@bgm_year", info.BgmYear);
                dbManager.AddParameters(15, "@expenses", info.BeExpenses);
                dbManager.AddParameters(16, "@emp_seq", info.InfoDeliveryEmp.Seq);
                dbManager.AddParameters(17, "@job_site_seq", info.InfoDeliveryJobSite.Seq);
                dbManager.AddParameters(18, "@record_status", info.InfoRecord.InfoStatus.Status);
                dbManager.AddParameters(19, "@request_month", info.DeliveryDate.Value.Month);
                dbManager.AddParameters(20, "@request_year", info.DeliveryDate.Value.Year);
                dbManager.AddParameters(21, "@created_by", info.InfoRecord.Created_by);
                dbManager.AddParameters(22, "@created_when", info.InfoRecord.Created_when);

                strSQL = "INSERT INTO ICM_DELIVERY_NOTE("
                            + "ICM_PO_SEQ"
                            + ", ICM_PO_INSTALL_SEQ"
                            + ", DELIVERY_DATE"
                            + ", DELIVERY_EMP_SEQ"
                            + ", DELIVERY_JOB_SITE_SEQ"
                            + ", ICM_DN_NAME"
                            + ", ICM_DN_REF_NO"
                            + ", ICM_DN_DESCR"
                            + ", RECEIVE_DATE"
                            + ", INVOICE_NUMBER"
                            + ", INVOICE_DATE"
                            + ", ICM_WH_SEQ"
                            + ", DOCUMENT_NUMBER"
                            + ", REDEMPTION_AMOUNT"
                            + ", DEDUCTION_AMOUNT"
                            + ", DELIVERY_PLACE"
                            + ", YEAR_TYPE_SEQ"
                            + ", BGM_YEAR"
                            + ", EXPENSES"
                            + ", RECORD_STATUS"
                            + ", CREATED_BY"
                            + ", CREATED_WHEN) "
                        + " VALUES ("
                            + " @po_seq"
                            + ", @install_seq"
                            + ", @date"
                            + ", @emp_seq"
                            + ", @job_site_seq"
                            + ", @name"
                            + ", CONCAT('DN-', @request_year + 543, right(concat('00',@request_month),2), '-'"
                                + ", (select right("
                                    + " concat('0000'"
                                        + ", coalesce("
                                            + "right("
                                                + "(select max(sub.icm_dn_ref_no)"
                                                + " from icm_delivery_note sub"
                                                + " where month(sub.delivery_date) = @request_month"
                                                    + " and year(sub.delivery_date) = @request_year"
                                                + ")"
                                            + ", 4)"
                                        + ", 0)"
                                    + " + 1)"
                                + ",4)"
                            + "))"
                            + ", @descr"
                            + ", @receive_date"
                            + ", @invoice_no"
                            + ", @invoice_date"
                            + ", @wh_seq"
                            + ", @doc_no"
                            + ", @redemption_amount"
                            + ", @deduction_amount"
                            + ", @place"
                            + ", @year_type_seq"
                            + ", @bgm_year"
                            + ", @expenses"
                            + ", @record_status"
                            + ", @created_by"
                            + ", @created_when)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                strSQL = "SELECT LAST_INSERT_ID()"
                      + " FROM ICM_DELIVERY_NOTE";
                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    lastSeq = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
                }

                //Insert [icm_delivery_note_item] --------------------------------------------
                dalDnItem.Insert(infoListItem, lastSeq, info, dbManager);

                //Insert [icm_delivery_note_item_extra] --------------------------------------------
                dalDnExtra.Insert(infoListExtra, lastSeq, info, dbManager);

                // Insert [icm_delivery_note_committee] ------------------------------------------------
                dalDnCommittee.Insert(infoListCommittee, lastSeq, info, dbManager);

                // Insert [icm_delivery_note_penalty] -------------------------------------------------
                dalDnPenalty.Insert(infoListPenalty, lastSeq, info, dbManager);

                // Insert [icm_delivery_note_file] ----------------------------------------------------
                dalDocument.InsertFileToDB(infoListFile, "ICM_DELIVERY_NOTE_FILE", "ICM_DN_SEQ", lastSeq, dbManager);

                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@lastSeq", lastSeq);

                strSQL = "SELECT ICM_DN_REF_NO"
                    + " FROM ICM_DELIVERY_NOTE"
                    + " WHERE ICM_DN_SEQ = @lastSeq";
                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    lastNumber = ds.Tables[0].Rows[0][0].ToString();
                }

                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
            return lastNumber;
        }

        public void DeleteAll(IList<DeliveryNoteInfo> infoList)
        {
            string strSQL;
            string seq = "";
            for (int i = 0; i < infoList.Count; i++)
            {
                if (i == 0)
                {
                    seq += infoList[i].Seq;
                }
                else
                {
                    seq += "," + infoList[i].Seq;
                }
            }

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@updated_by", infoList[0].InfoRecord.Updated_by);
                dbManager.AddParameters(2, "@updated_when", infoList[0].InfoRecord.Updated_when);

                strSQL = "UPDATE ICM_DELIVERY_NOTE"
                    + " SET RECORD_STATUS = @record_status"
                    + ", UPDATED_BY = @updated_by"
                    + ", UPDATED_WHEN = @updated_when"
                    + " WHERE ICM_DN_SEQ IN (" + seq + ")";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update(DeliveryNoteInfo info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(13);

                dbManager.AddParameters(0, "@dn_seq", info.Seq);
                dbManager.AddParameters(1, "@name", info.Name);
                dbManager.AddParameters(2, "@descr", info.Descr);
                dbManager.AddParameters(3, "@date", info.DeliveryDate);
                dbManager.AddParameters(4, "@receive_date", info.ReceiveDate);
                dbManager.AddParameters(5, "@invoice_no", info.InvoiceNumber);
                dbManager.AddParameters(6, "@invoice_date", info.InvoiceDate);
                dbManager.AddParameters(7, "@wh_seq", info.InfoWarehouse.Seq);
                dbManager.AddParameters(8, "@doc_no", info.DocumentNumber);
                dbManager.AddParameters(9, "@place", info.DeliveryPlace);
                dbManager.AddParameters(10, "@expenses", info.BeExpenses);
                dbManager.AddParameters(11, "@updated_by", info.InfoRecord.Updated_by);
                dbManager.AddParameters(12, "@updated_when", info.InfoRecord.Updated_when);

                strSQL = "UPDATE ICM_DELIVERY_NOTE"
                    + " SET DELIVERY_DATE = @date"
                    + ", ICM_DN_NAME = @name"
                    + ", ICM_DN_DESCR = @descr"
                    + ", RECEIVE_DATE = @receive_date"
                    + ", INVOICE_NUMBER = @invoice_no"
                    + ", INVOICE_DATE = @invoice_date"
                    + ", ICM_WH_SEQ = @wh_seq"
                    + ", DOCUMENT_NUMBER = @doc_no"
                    + ", DELIVERY_PLACE = @place"
                    + ", EXPENSES = @expenses"
                    + ", UPDATED_BY = @updated_by"
                    + ", UPDATED_WHEN = @updated_when"
                    + " WHERE ICM_DN_SEQ = @dn_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public string UpdateTotalAndAmountConfirm(DeliveryNoteInfo info, IList<PrePurchaseRequestItemInfo> infoListPprItem, IList<DeliveryNoteRouteInfo> infoListRoute)
        {
            PrePurchaseRequestItemDAL dalPpritem = new PrePurchaseRequestItemDAL();
            string returnTxt = "";
            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dalPpritem.UpdateAmountForDeliveryNote(infoListPprItem, dbManager);
                returnTxt = dalPpritem.CheckPurchaseRequestMoneyForDeliveryNote(Convert.ToInt32(info.Seq), dbManager);

                if (returnTxt != "0")
                {
                    dbManager.RollBackTransaction();
                    return "กรุณาตรวจสอบจำนวนและวงเงิน";
                }

                Confirm(info, infoListRoute, dbManager);
                AutoInsertMoneyRequest(info, dbManager);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
            return returnTxt;
        }

        public void Confirm(DeliveryNoteInfo info, IList<DeliveryNoteRouteInfo> infoListRoute, IDBManager dbManagerTrans)
        {
            string strSQL;
            DeliveryNoteRouteDAL dalRoute = new DeliveryNoteRouteDAL();
            try
            {
                dbManagerTrans.CreateParameters(4);

                dbManagerTrans.AddParameters(0, "@seq", info.Seq);
                dbManagerTrans.AddParameters(1, "@confirm_emp_seq", info.InfoConfirmEmp.Seq);
                dbManagerTrans.AddParameters(2, "@confirm_date", info.ConfirmDate);
                dbManagerTrans.AddParameters(3, "@record_status", SystemInfo._ApproveRecordStatus);//SystemInfo._ProcessRecordStatus);

                strSQL = "UPDATE ICM_DELIVERY_NOTE"
                    + " SET CONFIRM_EMP_SEQ = @confirm_emp_seq"
                    + ", CONFIRM_DATE = @confirm_date"
                    + ", RECORD_STATUS = @record_status"
                    + " WHERE ICM_DN_SEQ = @seq";

                dbManagerTrans.ExecuteNonQuery(CommandType.Text, strSQL);

                dbManagerTrans.CreateParameters(4);
                dbManagerTrans.AddParameters(0, "@seq", info.Seq);
                dbManagerTrans.AddParameters(1, "@created_by", info.InfoConfirmEmp.Seq);
                dbManagerTrans.AddParameters(2, "@created_when", DateTime.Now);
                dbManagerTrans.AddParameters(3, "@record_status", SystemInfo._ApproveRecordStatus);

                strSQL = "INSERT INTO BGM_PROJECT_OPERATION_REPORT"
                    + "(DESCR, BGM_PROJ_OPER_EXP_SEQ, ICM_DN_ITEM_SEQ, USED_AMOUNT, CAL_AMOUNT, RPT_TYPE, RECORD_STATUS, CREATED_BY, CREATED_WHEN)"
                    + "(SELECT DISTINCT (select icm_dn_name from icm_delivery_note where icm_dn_seq = @seq), pri.BGM_PROJ_OPER_EXP_SEQ, ni.ICM_PO_INSTALL_ITEM_SEQ, COALESCE(ni.NET_AMOUNT,0)"
                        + ", COALESCE(- (ni.NET_AMOUNT),0), 6, @record_status, @created_by, @created_when"
                    + " FROM ICM_DELIVERY_NOTE_ITEM ni"
                        + " INNER JOIN ICM_PURCHASE_ORDER_INSTALLMENT_ITEM ii ON ni.ICM_PO_INSTALL_ITEM_SEQ = ii.ICM_PO_INSTALL_ITEM_SEQ"
                        + " INNER JOIN ICM_PURCHASE_ORDER_INSTALLMENT oi ON ii.ICM_PO_INSTALL_SEQ = oi.ICM_PO_INSTALL_SEQ"
                        + " INNER JOIN ICM_BIDDER_QUOTATION q ON ii.ICM_BIDDER_QUO_SEQ = q.ICM_BIDDER_QUO_SEQ"
                        + " INNER JOIN ICM_PRE_PURCHASE_REQUEST_ITEM pri ON q.ICM_PR_ITEM_SEQ = pri.ICM_PR_ITEM_SEQ"
                    + " WHERE ni.ICM_DN_SEQ = @seq)";

                dbManagerTrans.ExecuteNonQuery(CommandType.Text, strSQL);

                dbManagerTrans.CreateParameters(4);
                dbManagerTrans.AddParameters(0, "@seq", info.Seq);
                dbManagerTrans.AddParameters(1, "@created_by", info.InfoConfirmEmp.Seq);
                dbManagerTrans.AddParameters(2, "@created_when", DateTime.Now);
                dbManagerTrans.AddParameters(3, "@yes_status", SystemInfo._YesStatus);

                strSQL = "UPDATE BGM_PROJECT_OPERATION_REPORT"
                    + " SET CAL_AMOUNT = 0"
                    + ", UPDATED_BY = @created_by"
                    + ", UPDATED_WHEN = @created_when"
                    + " WHERE ICM_PO_INSTALL_ITEM_SEQ IS NOT NULL"
                        + " AND ICM_PO_INSTALL_ITEM_SEQ IN"
                            + " (select ni.icm_po_install_item_seq"
                            + " from icm_delivery_note_item ni"
                                + " inner join icm_purchase_order_installment_item oii on ni.icm_po_install_item_seq = oii.icm_po_install_item_seq"
                                + " inner join icm_purchase_order_installment oi on oii.icm_po_install_seq = oi.icm_po_install_seq"
                                + " inner join icm_bidder_quotation q on oii.icm_bidder_quo_seq = q.icm_bidder_quo_seq"
                                + " inner join icm_purchase_request_item pr on q.icm_pr_item_seq = pr.icm_pr_item_seq"
                            + " where ni.icm_dn_seq = @seq"
                                + " and q.selected = @yes_status)";

                dbManagerTrans.ExecuteNonQuery(CommandType.Text, strSQL);
                dalRoute.Insert(infoListRoute, (int)info.Seq, (int)info.InfoConfirmEmp.Seq, dbManagerTrans);
            }
            catch
            {
                throw;
            }
        }

        public void AutoInsertMoneyRequest(DeliveryNoteInfo info, IDBManager dbManagerTrans)
        {
            string strSQL;
            try
            {
                dbManagerTrans.CreateParameters(2);

                dbManagerTrans.AddParameters(0, "@dn_seq", info.Seq);
                dbManagerTrans.AddParameters(1, "@dn_emp_seq", info.InfoConfirmEmp.Seq);

                strSQL = "CALL P_Auto_Insert_Money_Request_From_Delivery_Note(@dn_seq, @dn_emp_seq)";

                dbManagerTrans.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
        }
    }
}
